FTD Over Time Case Study

Author

Pavlina Novakova

Goal: Provide information about First Time Deposits (FTD) over time on client level.

FTD is calculated as the first deposit a client made across all of their logins.

Code
import pandas as pd
import numpy as np

import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

import plotly.io as pio
pio.renderers.default = "notebook"
Code
daily_ftd = pd.read_csv('./../../data/report/daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])
monthly_ftd = pd.read_csv('./../../data/report/monthly_ftd.csv', index_col=0)
forecast_ftd = pd.read_csv('./../../data/report/forecast_daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])

1. FTD Key Metrics

Metric Value
Total FTDs 5,294
Total FTD Volume $3,006,417 USD
Average FTD $568 USD
Median FTD $316 USD
FTD Conversion Rate 99.4% (5,294 / 5,325 clients)

FTD amounts are right-skewed (mean >> median), indicating a small number of high-value first deposits pulling the average up.

2. FTD Over Time - Daily

Code
fig = make_subplots(rows=2, cols=1, subplot_titles=('Daily FTD Count', 'Daily FTD Volume (USD)'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Count'), row=1, col=1)
fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Volume'), row=2, col=1)

fig.update_layout(height=500, showlegend=False)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

Daily FTD count shows high variability with weekday/weekend patterns. Volume follows a similar shape but with more pronounced spikes from individual high-value deposits.

3. FTD Over Time - Monthly

Code
fig = px.bar(monthly_ftd, x='ftd_month', y='count',
             title='Monthly FTD Count',
             labels={'ftd_month': 'Month', 'count': 'FTD Count'})
fig.update_layout(height=400)
fig.show()

fig = px.bar(monthly_ftd, x='ftd_month', y='volume',
             title='Monthly Volume FTD Amount (USD)',
             labels={'ftd_month': 'Month', 'volume': 'Volume (USD)'})
fig.update_layout(height=400)
fig.show()

display(monthly_ftd[['ftd_month', 'count', 'volume', 'avg_amount']])
ftd_month count volume avg_amount
0 2025-01 876 487815.769037 556.867316
1 2025-02 666 429361.021236 644.686218
2 2025-03 358 230712.599879 644.448603
3 2025-04 402 299541.733613 745.128691
4 2025-05 357 252317.334185 706.771244
5 2025-06 370 158618.551086 428.698787
6 2025-07 252 124290.961312 493.218100
7 2025-08 756 291496.712857 385.577663
8 2025-09 513 247469.630934 482.396941
9 2025-10 211 129346.309601 613.015685
10 2025-11 106 60465.904713 570.433063

Monthly FTD count is highest in January (876) and August (756). Later months show fewer FTDs as the observation window narrows (data ends Nov 2025). Average FTD amount fluctuates between $386–$745/month with no clear trend.

4. FTD by Registration Type

Type FTD Count Avg FTD (USD) Share
Full 4,504 $574 85.1%
Demo 427 $691 8.1%
Light 363 $344 6.9%

Full registrations account for the vast majority of FTDs. Demo accounts have the highest average FTD, while Light registrations have the lowest.

5. FTD Forecast (Next 30 Days)

Code
fig = make_subplots(rows=2, cols=1, subplot_titles=('FTD Count: Actual + Forecast', 'FTD Volume: Actual + Forecast'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Actual'), row=1, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['count'], 
                         mode='lines', name='Forecast', line=dict(dash='dash', color='red')), row=1, col=1)

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Actual', showlegend=False), row=2, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['volume'], 
                         mode='lines', name='Forecast', showlegend=False, line=dict(dash='dash', color='red')), row=2, col=1)

fig.update_layout(height=500)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

display(f'30-day forecast: {forecast_ftd["count"].sum():.0f} FTDs, ${forecast_ftd["volume"].sum():,.0f} volume')
'30-day forecast: 76 FTDs, $342,541 volume'

A rolling MA(5) model was used for the 30-day forecast (best performer among MA windows 3–21, MAE = 3.25, RMSE = 3.86). The forecast converges to ~2.5 FTDs/day (~$11,650/day), producing a conservative estimate of 76 FTDs and $342,541 in total volume for December 2025. The convergence is an inherent limitation of moving average forecasting — it cannot capture trends or seasonality.

6. Additional Findings

  • Account structure: 74% of clients have a single trading account; average is 1.47 logins per client.
  • Total transaction volume: $4.55M USD across 7,763 transactions (including non-FTD deposits).
  • Temporal patterns: Transaction activity is concentrated on weekdays with a notable drop on weekends.
  • Data quality: After cleaning, referential integrity holds across all tables. Transaction amounts were converted to USD using ECB historical rates.

7. Limitations

  • Data covers Jan–Nov 2025 only; later months have fewer observations.
  • The near-100% FTD conversion rate suggests this dataset represents active/depositing clients, not the full client population.
  • MA forecasts converge to a flat value and cannot model trends or seasonality. More advanced methods (ARIMA, Prophet) would be needed for longer-horizon predictions.
  • All amounts converted to USD using ECB rates — minor discrepancies possible for exotic currencies.